﻿Public Class ListInOut4

    Private Sub LoadData()
        Dim dt As New DataTable
        Dim dc As DataColumn
        dt.Columns.Add("名称")
        dc = dt.Columns.Add("金额", GetType(Decimal))
        dc.DefaultValue = 0
        dc = dt.Columns.Add("初期金额", GetType(Decimal))
        dc.DefaultValue = 0
        dc = dt.Columns.Add("直接收入", GetType(Decimal))
        dc.DefaultValue = 0
        dc = dt.Columns.Add("直接支出", GetType(Decimal))
        dc.DefaultValue = 0
        dc = dt.Columns.Add("账户间转入", GetType(Decimal))
        dc.DefaultValue = 0
        dc = dt.Columns.Add("账户间转出", GetType(Decimal))
        dc.DefaultValue = 0
        dc = dt.Columns.Add("冲账金额", GetType(Decimal))
        dc.DefaultValue = 0
        dc = dt.Columns.Add("合计", GetType(Decimal))
        dc.DefaultValue = 0
        dc = dt.Columns.Add("差额", GetType(Decimal))
        dc.DefaultValue = 0
        dt.Columns.Add("备注")

        Dim strSql As String = String.Empty

        strSql = "select id,Title as 名称,count as 金额,InitCount as 初期金额,memo as 备注 ,0.0 as 直接收入,0.0 as 直接支出,0.0 as 账户间转入,0.0 as 账户间转出,0.0 as 合计,0.0 as 差额,0.0 as 借出金额,0.0 as 借入金额,0.0 as 还入金额,0.0 as 还出金额,0.0 as 冲账金额 from CrashStatus order by 名称 "

        Dim dt2 As DataTable = ct.FillTable(strSql)


        Dim total As Decimal = 0
        Dim 借出金额 As Decimal = 0
        Dim 还入金额 As Decimal = 0
        Dim 借入金额 As Decimal = 0
        Dim 还出金额 As Decimal = 0
        Dim 直接收入 As Decimal = 0
        Dim 直接支出 As Decimal = 0
        Dim 账户间转入 As Decimal = 0
        Dim 账户间转出 As Decimal = 0
        Dim 初期金额 As Decimal = 0
        Dim 冲账金额 As Decimal = 0

        For Each dr As DataRow In dt2.Rows
            '2010-12-12
            初期金额 += CDec(dr("初期金额"))
            Dim dt3 As DataTable
            strSql = "select sum(CrashStatusCount) as count from CrashInOut where type=0 and CrashStatusID='" + CStr(dr("id")) + "' AND date(CrashInOut.DATETIME)>='2010-12-13'"
            dt3 = ct.FillTable(strSql)
            If dt3.Rows.Count > 0 Then
                dr("直接收入") = 结果(dt3.Rows(0)("count"))
                直接收入 += 结果(dt3.Rows(0)("count"))
            End If

            strSql = "select sum(CrashStatusCount) as count from CrashInOut where type=1 and CrashStatusID='" + CStr(dr("id")) + "' AND date(CrashInOut.DATETIME)>='2010-12-13'"
            dt3 = ct.FillTable(strSql)
            If dt3.Rows.Count > 0 Then
                dr("直接支出") = 结果(dt3.Rows(0)("count"))
                直接支出 += 结果(dt3.Rows(0)("count"))
            End If

            strSql = "select sum(CrashStatusCount) as count from CrashInOut where type=2 and CrashStatusID='" + CStr(dr("id")) + "' AND date(CrashInOut.DATETIME)>='2010-12-13'"
            dt3 = ct.FillTable(strSql)
            If dt3.Rows.Count > 0 Then
                dr("账户间转入") = 结果(dt3.Rows(0)("count"))
                账户间转入 += 结果(dt3.Rows(0)("count"))
            End If

            strSql = "select sum(CrashStatusCount) as count from CrashInOut where type=2 and InOutID='" + CStr(dr("id")) + "' AND date(CrashInOut.DATETIME)>='2010-12-13'"
            dt3 = ct.FillTable(strSql)
            If dt3.Rows.Count > 0 Then
                dr("账户间转出") = 结果(dt3.Rows(0)("count"))
                账户间转出 += 结果(dt3.Rows(0)("count"))
            End If

            strSql = "select sum(CrashStatusCount) as count from CrashInOut where type=3  and CrashStatusID='" + CStr(dr("id")) + "' AND date(CrashInOut.DATETIME)>='2010-12-13'"
            dt3 = ct.FillTable(strSql)
            If dt3.Rows.Count > 0 Then
                dr("借出金额") = 结果(dt3.Rows(0)("count"))
                借出金额 += CDec(dr("借出金额"))
            End If

            strSql = "select sum(CrashStatusCount) as count from CrashInOut where type=4 and  CrashStatusID='" + CStr(dr("id")) + "' AND date(CrashInOut.DATETIME)>='2010-12-13'"
            dt3 = ct.FillTable(strSql)
            If dt3.Rows.Count > 0 Then
                dr("还入金额") = 结果(dt3.Rows(0)("count"))
                还入金额 += CDec(dr("还入金额"))
            End If

            strSql = "select sum(CrashStatusCount) as count from CrashInOut where type=6 and  CrashStatusID='" + CStr(dr("id")) + "' AND date(CrashInOut.DATETIME)>='2010-12-13'"
            dt3 = ct.FillTable(strSql)
            If dt3.Rows.Count > 0 Then
                dr("冲账金额") = 结果(dt3.Rows(0)("count"))
                冲账金额 += CDec(dr("冲账金额"))
            End If

            dr("合计") = 结果(dr("直接收入")) + 结果(dr("账户间转入")) - 结果(dr("直接支出")) - 结果(dr("账户间转出")) - 结果(dr("冲账金额"))
            dr("差额") = 结果(dr("金额")) - 结果(dr("合计")) - 结果(dr("初期金额"))

            total += CDec(dr("金额"))

        Next

        '实际金额=各账户余额

        Dim 实际借出 As Decimal = 0
        Dim dt实际借出 As DataTable = Get借出一览()
        If dt实际借出.Rows.Count > 0 Then
            实际借出 = CDec(dt实际借出.Compute("SUM(金额)", ""))
        End If

        Dim 未还一览 As Decimal = 0
        Dim dt未还一览 As DataTable = Get未还一览()
        If dt未还一览.Rows.Count > 0 Then
            未还一览 = dt未还一览.Rows(0)(0)
        End If

        Dim dgr As DataRow = dt2.NewRow
        dgr("id") = "0"
        dgr("名称") = "合计"
        dgr("金额") = total
        dgr("备注") = "实际(账户余额+别人欠钱-欠别人钱)"

        'dgr("直接收入") = 直接收入
        'dgr("直接支出") = 直接支出
        'dgr("借出金额") = 借出金额
        dgr("还入金额") = 实际借出
        dgr("还出金额") = 未还一览
        dgr("初期金额") = total + 实际借出 - 未还一览
        'dgr("冲账金额") = 冲账金额
        'dgr("合计") = total - 还入金额 + 借出金额
        dt2.Rows.Add(dgr)

        dgr = dt2.NewRow
        '数据库结余=收入-直接支出#############################################
        Dim 数据库结余 As Decimal = 0

        '直接收入
        Dim dt1 As DataTable = ct.FillTable("select  sum(count) as count from WysInput ")
        If dt1.Rows.Count > 0 Then
            数据库结余 = CDec(dt1.Rows(0)("count"))
            dgr("直接收入") = CDec(dt1.Rows(0)("count"))
        End If

        '直接支出
        dt1 = ct.FillTable("select  sum(count) as count from WysOutput  ")
        If dt1.Rows.Count > 0 Then
            数据库结余 -= CDec(dt1.Rows(0)("count"))
            dgr("直接支出") = CDec(dt1.Rows(0)("count"))
        End If

        '借出
        dt1 = ct.FillTable("select  sum(count) as count from WysBorrowLend  where type = 0 ")
        If dt1.Rows.Count > 0 Then
            数据库结余 -= CDec(dt1.Rows(0)("count"))
            dgr("借出金额") = CDec(dt1.Rows(0)("count"))
        End If

        '还入
        dt1 = ct.FillTable("select  sum(count) as count from WysBorrowLend  where type = 1  ")
        If dt1.Rows.Count > 0 Then
            数据库结余 += CDec(dt1.Rows(0)("count"))
            dgr("还入金额") = CDec(dt1.Rows(0)("count"))
        End If

        '冲账金额
        dt1 = ct.FillTable("select  sum(count) as count from CrashToCrash  ")
        If dt1.Rows.Count > 0 Then
            数据库结余 -= CDec(dt1.Rows(0)("count"))
            dgr("冲账金额") = CDec(dt1.Rows(0)("count"))
        End If


        数据库结余 = 数据库结余
        '数据库结余=收入-直接支出############################################

        dgr("id") = "0"
        dgr("名称") = "结余"
        dgr("金额") = 数据库结余
        dgr("备注") = "基于数据库"
        dgr("初期金额") = 初期金额
        dgr("账户间转出") = 账户间转出
        dgr("账户间转入") = 账户间转入

        'dgr("合计") = 初期金额 - 直接支出 + 直接收入 + 账户间转出 - 账户间转入 + 还入金额 - 冲账金额
        dt2.Rows.Add(dgr)

        dgr = dt2.NewRow
        dgr("id") = "0"
        dgr("初期金额") = 初期金额
        dgr("名称") = "差额"
        dgr("金额") = (total - 数据库结余).ToString
        dgr("备注") = "实际-数据库结余（负为少了 正为多了）"
        'dgr("直接支出") = 直接收入 - 直接支出
        'dgr("账户间转出") = 账户间转入 - 账户间转出
        dgr("还入金额") = 借出金额 - 还入金额
        'dgr("合计") = total - 还入金额 + 借出金额 - (初期金额 - 直接支出 + 直接收入 + 账户间转出 - 账户间转入 + 还入金额) + 冲账金额
        dt2.Rows.Add(dgr)

        Me.DataGridView1.DataSource = dt2

        Dim width As Integer = 0
        For i As Integer = 0 To Me.DataGridView1.ColumnCount - 1
            If Me.DataGridView1.Columns(i).Visible = True Then
                width += Me.DataGridView1.Columns(i).Width
            End If
        Next
        Me.Width = width + 40
    End Sub

    Private Sub ListInOut_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.DataGridView1.AutoGenerateColumns = False
        LoadData()

    End Sub

    Public Function 结果(ByVal ob As Object) As Decimal
        If ob Is Nothing Then
            Return 0
        End If
        If ob Is DBNull.Value Then
            ob = 0
        End If
        Return CDec(ob)
    End Function

    Private Sub DataGridView1_CellContentDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentDoubleClick
       
    End Sub

    Private Sub DataGridView1_CellDoubleClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellDoubleClick
        If e.RowIndex < 0 Then Exit Sub
        If e.ColumnIndex < 0 Then Exit Sub

        If Me.DataGridView1.SelectedCells.Count = 0 Then Exit Sub
        If Me.DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString = "0" Then
            Exit Sub
        End If

        If e.ColumnIndex = 0 OrElse e.ColumnIndex = 1 OrElse e.ColumnIndex = 2 OrElse e.ColumnIndex = 3 Then
            Using frm As MoneyStatus = New MoneyStatus
                frm.参数列表.Add("ID", Me.DataGridView1.Rows(e.RowIndex).Cells(0).Value.ToString)
                frm.模式 = Wys.Control.Vb.InsertFormBase.模式型.修正
                frm.iType = Me.iType
                frm.strName = Me.strName
                frm.ShowDialog()
                If frm.bAdd Then
                    LoadData()
                End If
            End Using
        ElseIf e.ColumnIndex = 4 Then

        End If

    End Sub

    Private Function Get借出一览() As DataTable

        Dim sb As New System.Text.StringBuilder
        sb.AppendLine("Select ifnull(sum(count),0) as '金额' ")
        sb.AppendLine("from WysBorrowLend")
        sb.AppendLine("LEFT JOIN WysInputUser")
        sb.AppendLine("on LendBorrowUserName=WysInputUser.ID")
        sb.AppendLine("where WysBorrowLend.Type=0 and WysBorrowLend.Status=0")

        sb.AppendLine("UNION ")
        sb.AppendLine("Select ifnull((w1.Count-sum(w2.Count)),0) as 金额")
        sb.AppendLine("FROM  WysBorrowLend w1")
        sb.AppendLine("LEFT JOIN WysBorrowLend w2")
        sb.AppendLine("On w1.id=w2.LendBorrowUserName")
        sb.AppendLine("WHERE w1.Status=2 and w2.Type=1")

        Return ct.FillTable(sb.ToString())

    End Function

    Private Function Get未还一览() As DataTable

        Dim sb As New System.Text.StringBuilder
        sb.AppendLine("Select ifnull(sum(count),0) as '金额' ")
        sb.AppendLine(",count as '金额',WysBorrowLend.Memo as '备注' ")
        sb.AppendLine("from WysBorrowLend")
        sb.AppendLine("LEFT JOIN WysInputUser")
        sb.AppendLine("on LendBorrowUserName=WysInputUser.ID")
        sb.AppendLine("where WysBorrowLend.Type=2 and WysBorrowLend.Status=0")

        Return ct.FillTable(sb.ToString())

    End Function

End Class
